package com.fau.fm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.fau.fm.entity.MedicalAdmin;
import com.fau.fm.entity.MedicalLogistics;
import com.fau.fm.entity.MedicalLogisticsStatistics;
import com.fau.fm.entity.MedicalOrderHasBackSection;
import com.fau.fm.entity.MedicalOrderHasReconciliation;
import com.fau.fm.entity.MedicalOrdersStorage;
import com.fau.fm.entity.MedicalProductStatistics;
import com.fau.fm.entity.MedicalProductStorage;
import com.fau.fm.service.IStatisticsManagerService;
import com.fau.fm.util.DateUtil;
import com.fau.fm.util.Page;
import com.fau.fm.util.ParamUtil;
import com.fau.fm.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class StatisticsManagerService extends BaseService implements IStatisticsManagerService {

	@Override
	public Page findAllOrderReconciliation(Map<String, Object> map, MedicalOrderHasReconciliation order) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrderHasReconciliation m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrderHasReconciliation m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(order.getHospitalId());
		objectList.add(order.getPayType());
		
		if(null != order.getStartTime() && null != order.getEndTime()) {
			statusSql.append(" and m.createTime between ? and ? ");
			countSql.append(" and m.createTime between ? and ? ");
			objectList.add(order.getStartTime());
			objectList.add(order.getEndTime());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append(" order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	public Page findAllOrderBackSection(Map<String, Object> map, MedicalOrderHasBackSection order) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrderHasBackSection m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrderHasBackSection m where 1=1 and m.hospitalId = ? and m.payType = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(order.getHospitalId());
		objectList.add(order.getPayType());
		
		if(null != order.getStartTime() && null != order.getEndTime()) {
			statusSql.append(" and m.createTime between ? and ? ");
			countSql.append(" and m.createTime between ? and ? ");
			objectList.add(order.getStartTime());
			objectList.add(order.getEndTime());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append(" order by m.createTime desc ");
		}
		
		return this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}

	@Override
	public Workbook selectedPage(MedicalOrderHasReconciliation order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(order.getId())){
			String [] ids =  order.getId().split(",");
			for(int j=0;j<ids.length;j++){
				// 获得记录
				MedicalOrderHasReconciliation bean = (MedicalOrderHasReconciliation)this.iBaseDao.getObject(MedicalOrderHasReconciliation.class, ids[j]);
				if(null != bean){
					Row currentRow = sheet.createRow(j+1);
					currentRow.createCell(0).setCellValue(j+1);
					currentRow.createCell(1).setCellValue(bean.getSerialNum());
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(bean.getName());
					if(null != bean.getPayment()){
						currentRow.createCell(4).setCellValue(bean.getPayment().toString());
					}
					if(null != bean.getFreight()){
						currentRow.createCell(5).setCellValue(bean.getFreight().toString());
					}
					if(null != bean.getSignTime()){
						currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
					}
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook allPage(MedicalOrderHasReconciliation order) throws Exception {
		List<MedicalOrderHasReconciliation> list = this.iBaseDao.getBySql("from MedicalOrderHasReconciliation m where 1=1 and m.hospitalId = ? ", null, order.getHospitalId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalOrderHasReconciliation bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(bean.getName());
				currentRow.createCell(4).setCellValue(bean.getPayment().toString());
				currentRow.createCell(5).setCellValue(bean.getFreight().toString());
				if(null != bean.getSignTime()){
					currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}

	@Override
	public Workbook selectedPage(MedicalOrderHasBackSection order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(order.getId())){
			String [] ids =  order.getId().split(",");
			for(int j=0;j<ids.length;j++){
				// 获得记录
				MedicalOrderHasBackSection bean = (MedicalOrderHasBackSection)this.iBaseDao.getObject(MedicalOrderHasBackSection.class, ids[j]);
				if(null != bean){
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(bean.getSerialNum());
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(bean.getName());
					currentRow.createCell(4).setCellValue(bean.getPayment().toString());
					currentRow.createCell(5).setCellValue(bean.getFreight().toString());
					if(null != bean.getSignTime()){
						currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
					}
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook allPage(MedicalOrderHasBackSection order) throws Exception {
		List<MedicalOrderHasBackSection> list = this.iBaseDao.getBySql("from MedicalOrderHasBackSection m where 1=1 and m.hospitalId = ? ", null, order.getHospitalId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalOrderHasBackSection bean : list){
			i++;
			if(null != bean){
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(bean.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(bean.getName());
				currentRow.createCell(4).setCellValue(bean.getPayment().toString());
				currentRow.createCell(5).setCellValue(bean.getFreight().toString());
				if(null != bean.getSignTime()){
					currentRow.createCell(6).setCellValue(DateFormatUtils.format(bean.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}

	@Override
	public Page findAllOrderReconSta(Map<String, Object> map, MedicalOrderHasReconciliation order) {
		StringBuilder statusSql = new StringBuilder("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from MedicalOrderHasReconciliation m where 1=1 and m.cusSerId in (select id from MedicalAdmin u) and m.hospitalId = ? ");
		StringBuilder countSql = new StringBuilder("select count(distinct m.cusSerId) from MedicalOrderHasReconciliation m where 1=1 and m.cusSerId in (select id from MedicalAdmin u) and m.hospitalId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(order.getHospitalId());
		
		if(null != order.getStartTime() && null != order.getEndTime()) {
			statusSql.append(" and m.createTime between ? and ? ");
			countSql.append(" and m.createTime between ? and ? ");
			objectList.add(order.getStartTime());
			objectList.add(order.getEndTime());
		}
		
		statusSql.append(" group by m.cusSerId ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalOrderHasReconciliation> list = new ArrayList<MedicalOrderHasReconciliation>();
		for(Object object : page.getResult()) {
			Object[] obj = (Object[]) object;
			MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
			MedicalOrderHasReconciliation bean = new MedicalOrderHasReconciliation();
			bean.setId(admin.getId());	//此处为用户id
			bean.setCusSerName(admin.getName());
			bean.setCountOrderNum(Integer.parseInt(obj[1].toString()));
			bean.setPayment(new BigDecimal(obj[2].toString()));
			bean.setFreight(new BigDecimal(obj[3].toString()));
			bean.setTotalMoney(new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString())));
			list.add(bean);
		}
		page.setResult(list);
		return page;
	}

	@Override
	public Page findAllOrderBackSectionSta(Map<String, Object> map, MedicalOrderHasBackSection order) {
		StringBuilder statusSql = new StringBuilder("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from MedicalOrderHasBackSection m where 1=1 and m.cusSerId in (select id from MedicalAdmin u) and m.hospitalId = ? ");
		StringBuilder countSql = new StringBuilder("select count(distinct m.cusSerId) from MedicalOrderHasBackSection m where 1=1 and m.cusSerId in (select id from MedicalAdmin u) and m.hospitalId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(order.getHospitalId());
		
		if(null != order.getStartTime() && null != order.getEndTime()) {
			statusSql.append(" and m.createTime between ? and ? ");
			countSql.append(" and m.createTime between ? and ? ");
			objectList.add(order.getStartTime());
			objectList.add(order.getEndTime());
		}
		
		statusSql.append(" group by m.cusSerId ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalOrderHasBackSection> list = new ArrayList<MedicalOrderHasBackSection>();
		for(Object object : page.getResult()) {
			Object[] obj = (Object[]) object;
			MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
			MedicalOrderHasBackSection bean = new MedicalOrderHasBackSection();
			bean.setId(admin.getId());	//此处为用户id
			bean.setCusSerName(admin.getName());
			bean.setCountOrderNum(Integer.parseInt(obj[1].toString()));
			bean.setPayment(new BigDecimal(obj[2].toString()));
			bean.setFreight(new BigDecimal(obj[3].toString()));
			bean.setTotalMoney(new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString())));
			list.add(bean);
		}
		page.setResult(list);
		return page;
	}

	@Override
	public Workbook selectedReconPage(MedicalOrderHasReconciliation order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_STA);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(order.getId())){
			String [] ids =  order.getId().split(",");
			if(null == order.getStartTime() && null == order.getEndTime()) {
				for(int j=0;j<ids.length;j++){
					// 获得记录
					Object object = this.iBaseDao.getObjectByHql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from MedicalOrderHasReconciliation m where 1=1 and m.cusSerId = ?", ids[j]);
					Object[] obj = (Object[]) object;
					MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(admin.getName());
					BigDecimal totalMoney = new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString()));
					currentRow.createCell(2).setCellValue(totalMoney.toString());
					currentRow.createCell(3).setCellValue(obj[2].toString());
					currentRow.createCell(4).setCellValue(obj[3].toString());
					currentRow.createCell(5).setCellValue(obj[1].toString());
				}
			} else {
				for(int j=0;j<ids.length;j++){
					// 获得记录
					Object object = this.iBaseDao.getObjectByHql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from MedicalOrderHasReconciliation m where 1=1 and m.cusSerId = ?"
							+ " and m.createTime between ? and ? ", ids[j], order.getStartTime(), order.getEndTime());
					Object[] obj = (Object[]) object;
					MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(admin.getName());
					BigDecimal totalMoney = new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString()));
					currentRow.createCell(2).setCellValue(totalMoney.toString());
					currentRow.createCell(3).setCellValue(obj[2].toString());
					currentRow.createCell(4).setCellValue(obj[3].toString());
					currentRow.createCell(5).setCellValue(obj[1].toString());
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook allReconPage(MedicalOrderHasReconciliation order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_STA);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		List<Object> list = new ArrayList<Object>();
		if(null == order.getStartTime() && null == order.getEndTime()) {
			list = this.iBaseDao.getBySql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from "
					+ "MedicalOrderHasReconciliation m where 1=1 and m.hospitalId = ? group by m.cusSerId", null, order.getHospitalId());
		} else {
			list = this.iBaseDao.getBySql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from "
					+ "MedicalOrderHasReconciliation m where 1=1 and m.hospitalId = ? and m.createTime between ? and ? group by m.cusSerId", 
					null, order.getHospitalId(), order.getStartTime(), order.getEndTime());
		}
		for(Object object : list){
			i++;
			if(null != object){
				Object[] obj = (Object[]) object;
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
				currentRow.createCell(1).setCellValue(admin.getName());
				BigDecimal totalMoney = new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString()));
				currentRow.createCell(2).setCellValue(totalMoney.toString());
				currentRow.createCell(3).setCellValue(obj[2].toString());
				currentRow.createCell(4).setCellValue(obj[3].toString());
				currentRow.createCell(5).setCellValue(obj[1].toString());
			}
		}
		return workbook;
	}

	@Override
	public Workbook selectedSectionPage(MedicalOrderHasBackSection order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_STA);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(order.getId())){
			String [] ids =  order.getId().split(",");
			if(null == order.getStartTime() && null == order.getEndTime()) {
				for(int j=0;j<ids.length;j++){
					// 获得记录
					Object object = this.iBaseDao.getObjectByHql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from MedicalOrderHasBackSection m where 1=1 and m.cusSerId = ?", ids[j]);
					Object[] obj = (Object[]) object;
					MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(admin.getName());
					BigDecimal totalMoney = new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString()));
					currentRow.createCell(2).setCellValue(totalMoney.toString());
					currentRow.createCell(3).setCellValue(obj[2].toString());
					currentRow.createCell(4).setCellValue(obj[3].toString());
					currentRow.createCell(5).setCellValue(obj[1].toString());
				}
			} else {
				for(int j=0;j<ids.length;j++){
					// 获得记录
					Object object = this.iBaseDao.getObjectByHql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from MedicalOrderHasBackSection m where 1=1 and m.cusSerId = ?"
							+ " and m.createTime between ? and ? ", ids[j], order.getStartTime(), order.getEndTime());
					Object[] obj = (Object[]) object;
					MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					currentRow.createCell(1).setCellValue(admin.getName());
					BigDecimal totalMoney = new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString()));
					currentRow.createCell(2).setCellValue(totalMoney.toString());
					currentRow.createCell(3).setCellValue(obj[2].toString());
					currentRow.createCell(4).setCellValue(obj[3].toString());
					currentRow.createCell(5).setCellValue(obj[1].toString());
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook allSectionPage(MedicalOrderHasBackSection order) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORTS_ORDERS_STA);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		List<Object> list = new ArrayList<Object>();
		if(null == order.getStartTime() && null == order.getEndTime()) {
			list = this.iBaseDao.getBySql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from "
					+ "MedicalOrderHasBackSection m where 1=1 and m.hospitalId = ? group by m.cusSerId", null, order.getHospitalId());
		} else {
			list = this.iBaseDao.getBySql("select m.cusSerId,count(m.cusSerId),sum(m.payment),sum(m.freight) from "
					+ "MedicalOrderHasBackSection m where 1=1 and m.hospitalId = ? and m.createTime between ? and ? group by m.cusSerId", 
					null, order.getHospitalId(), order.getStartTime(), order.getEndTime());
		}
		for(Object object : list){
			i++;
			if(null != object){
				Object[] obj = (Object[]) object;
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, obj[0].toString());
				currentRow.createCell(1).setCellValue(admin.getName());
				BigDecimal totalMoney = new BigDecimal(obj[2].toString()).add(new BigDecimal(obj[3].toString()));
				currentRow.createCell(2).setCellValue(totalMoney.toString());
				currentRow.createCell(3).setCellValue(obj[2].toString());
				currentRow.createCell(4).setCellValue(obj[3].toString());
				currentRow.createCell(5).setCellValue(obj[1].toString());
			}
		}
		return workbook;
	}

	@Override
	public Page findAllLogistics(Map<String, Object> map, MedicalLogisticsStatistics bean) {
		StringBuilder statusSql = new StringBuilder("from MedicalLogistics m where m.hospitalId = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalLogistics m where m.hospitalId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(bean.getHospitalId());
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalLogisticsStatistics> list = new ArrayList<MedicalLogisticsStatistics>();
		if(null == bean.getStartTime() || null == bean.getEndTime()) {
			bean.setStartTime(DateUtil.getDates(true, 0));
			bean.setEndTime(DateUtil.getDates(false, 0));
		}
		
		for(Object obj : page.getResult()) {
			MedicalLogistics log = (MedicalLogistics) obj;
			MedicalLogisticsStatistics logistics = new MedicalLogisticsStatistics();
			logistics.setLogisticsName(log.getName());
			
			// 发货部分
			Object object1 = this.iBaseDao.getObjectByHql("select count(m.expressId), sum(m.payment), sum(m.freight) from MedicalOrdersStorage m "
					+ " where m.expressId = ? and m.warehouseStatus = ? and m.createTime between ? and ? ", log.getId(), SystemConfig.HAS_DELIVERY,
					bean.getStartTime(), bean.getEndTime());
			Object[] obj1 = (Object[]) object1;
			logistics.setInvoiceNumber(Integer.parseInt(obj1[0].toString()));
			int invoiceProNumber = this.findProductNumber(bean, log, SystemConfig.HAS_DELIVERY, "1"); 	// 1代表仓储状态
			logistics.setInvoiceProNumber(invoiceProNumber);
			BigDecimal invoiceMoney = new BigDecimal(0);
			if(null != obj1[1]) {
				invoiceMoney = new BigDecimal(obj1[1].toString());
			}
			logistics.setInvoiceMoney(invoiceMoney);
			BigDecimal invoiceFreight = new BigDecimal(0);
			if(null != obj1[2]) {
				invoiceFreight = new BigDecimal(obj1[2].toString());
			}
			logistics.setInvoiceFreight(invoiceFreight);
			logistics.setInvoiceTotal(invoiceMoney.add(invoiceFreight));
			
			// 退单部分
			Object object2 = this.iBaseDao.getObjectByHql("select count(m.expressId), sum(m.payment), sum(m.freight) from MedicalOrdersStorage m "
					+ " where m.expressId = ? and m.warehouseStatus = ? and m.createTime between ? and ? ", log.getId(), SystemConfig.HAS_CHARGEBACK,
					bean.getStartTime(), bean.getEndTime());
			Object[] obj2 = (Object[]) object2;
			logistics.setBackNumber(Integer.parseInt(obj2[0].toString()));
			int backProNumber = this.findProductNumber(bean, log, SystemConfig.HAS_CHARGEBACK, "1");	// 1代表仓储状态
			logistics.setBackProNumber(backProNumber);
			BigDecimal backMoney = new BigDecimal(0);
			if(null != obj2[1]) {
				backMoney = new BigDecimal(obj2[1].toString());
			}
			logistics.setBackMoney(backMoney);
			BigDecimal backFreight = new BigDecimal(0);
			if(null != obj2[2]) {
				backFreight = new BigDecimal(obj2[2].toString());
			}
			logistics.setBackFreight(backFreight);
			logistics.setBackTotal(backMoney.add(backFreight));
			
			// 签收部分
			Object object3 = this.iBaseDao.getObjectByHql("select count(m.expressId), sum(m.payment), sum(m.freight) from MedicalOrdersStorage m "
					+ " where m.expressId = ? and m.warehouseStatus = ? and m.createTime between ? and ? ", log.getId(), SystemConfig.HAS_SIGNED,
					bean.getStartTime(), bean.getEndTime());
			Object[] obj3 = (Object[]) object3;
			logistics.setSignNumber(Integer.parseInt(obj3[0].toString()));
			int signProNumber = this.findProductNumber(bean, log, SystemConfig.HAS_SIGNED, "1");	// 1代表仓储状态
			logistics.setSignProNumber(signProNumber);
			BigDecimal signMoney = new BigDecimal(0);
			if(null != obj3[1]) {
				signMoney = new BigDecimal(obj3[1].toString());
			}
			logistics.setSignMoney(signMoney);
			BigDecimal signFreight = new BigDecimal(0);
			if(null != obj3[2]) {
				signFreight = new BigDecimal(obj3[2].toString());
			}
			logistics.setSignFreight(signFreight);
			logistics.setSignTotal(signMoney.add(signFreight));
			
			// 对账部分
			Object object4 = this.iBaseDao.getObjectByHql("select count(m.expressId), sum(m.payment), sum(m.freight) from MedicalOrdersStorage m "
					+ " where m.expressId = ? and m.financialStatus = ? and m.createTime between ? and ? ", log.getId(), SystemConfig.HAS_RECONCILED,
					bean.getStartTime(), bean.getEndTime());
			Object[] obj4 = (Object[]) object4;
			logistics.setReconNumber(Integer.parseInt(obj4[0].toString()));
			int reconProNumber = this.findProductNumber(bean, log, SystemConfig.HAS_RECONCILED, "2");	// 2代表财务状态
			logistics.setReconProNumber(reconProNumber);
			BigDecimal reconMoney = new BigDecimal(0);
			if(null != obj4[1]) {
				reconMoney = new BigDecimal(obj4[1].toString());
			}
			logistics.setReconMoney(reconMoney);
			BigDecimal reconFreight = new BigDecimal(0);
			if(null != obj4[2]) {
				reconFreight = new BigDecimal(obj4[2].toString());
			}
			logistics.setReconFreight(reconFreight);
			logistics.setReconTotal(reconMoney.add(reconFreight));
			
			// 回款部分
			Object object5 = this.iBaseDao.getObjectByHql("select count(m.expressId), sum(m.payment), sum(m.freight) from MedicalOrdersStorage m "
					+ " where m.expressId = ? and m.financialStatus = ? and m.createTime between ? and ? ", log.getId(), SystemConfig.HAS_SECTIONED,
					bean.getStartTime(), bean.getEndTime());
			Object[] obj5 = (Object[]) object5;
			logistics.setSectionNumber(Integer.parseInt(obj5[0].toString()));
			int sectionProNumber = this.findProductNumber(bean, log, SystemConfig.HAS_SECTIONED, "2");	// 2代表财务状态
			logistics.setSectionProNumber(sectionProNumber);
			BigDecimal sectionMoney = new BigDecimal(0);
			if(null != obj5[1]) {
				sectionMoney = new BigDecimal(obj5[1].toString());
			}
			logistics.setSectionMoney(sectionMoney);
			BigDecimal sectionFreight = new BigDecimal(0);
			if(null != obj5[2]) {
				sectionFreight = new BigDecimal(obj5[2].toString());
			}
			logistics.setSectionFreight(sectionFreight);
			logistics.setSectionTotal(sectionMoney.add(sectionFreight));
			
			// 总订单信息
			Integer totalNumber = logistics.getInvoiceNumber() + logistics.getBackNumber() + logistics.getSignNumber() + 
					logistics.getReconNumber() + logistics.getSectionNumber();
			
			Integer totalProNumber = logistics.getInvoiceProNumber() + logistics.getBackProNumber() + 
					logistics.getSignProNumber() + logistics.getReconProNumber() + logistics.getSectionProNumber();
			
			BigDecimal totalMoney = logistics.getInvoiceMoney().add(logistics.getBackMoney()).add(logistics.getSignMoney()).add(logistics.getReconMoney()).add(logistics.getSectionMoney());
			BigDecimal totalFreight = logistics.getInvoiceFreight().add(logistics.getBackFreight()).add(logistics.getSignFreight()).add(logistics.getReconFreight()).add(logistics.getSectionFreight());
			BigDecimal totalTotal = logistics.getInvoiceTotal().add(logistics.getBackTotal()).add(logistics.getSignTotal().add(logistics.getReconTotal())).add(logistics.getSectionTotal());
			
			logistics.setTotalNumber(totalNumber);
			logistics.setTotalProNumber(totalProNumber);
			logistics.setTotalMoney(totalMoney);
			logistics.setTotalFreight(totalFreight);
			logistics.setTotalTotal(totalTotal);
			
			list.add(logistics);
		}
		page.setResult(list);
		return page;
	}

	/**
	 * @desc 查询产品数量
	 * @param id
	 * @param status
	 * @param type
	 * @return int
	 * @author dangjh
	 * @datetime 2014年11月25日,下午7:11:19
	 */
	@SuppressWarnings("unchecked")
	private int findProductNumber(MedicalLogisticsStatistics bean, MedicalLogistics log, String status, String type) {
		int count = 0;
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		if("1".equals(type)) {
			list = this.iBaseDao.getBySql("from MedicalOrdersStorage m where m.expressId = ? and m.warehouseStatus = ? and m.createTime "
					+ "between ? and ? ", null, log.getId(), status, bean.getStartTime(), bean.getEndTime());
		} else {
			list = this.iBaseDao.getBySql("from MedicalOrdersStorage m where m.expressId = ? and m.financialStatus = ? and m.createTime "
					+ "between ? and ? ", null, log.getId(), status, bean.getStartTime(), bean.getEndTime());
		}
		for(MedicalOrdersStorage storage : list) {
			int productNumber = this.iBaseDao.getIntCount("select count(*) from MedicalOrdersProduct m where m.ordersId = ? ", storage.getId());
			count += productNumber;
		}
		return count;
	}
	
	@Override
	public Page findAllProducts(Map<String, Object> map, MedicalProductStatistics bean) {
		StringBuilder statusSql = new StringBuilder("from MedicalProductStorage m where m.hospitalId = ? ");
		StringBuilder countSql = new StringBuilder("select count(distinct m.productId) from MedicalProductStorage m where m.hospitalId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(bean.getHospitalId());
		statusSql.append(" and m.createTime >= ? ");
		countSql.append(" and m.createTime >= ? ");
		if(null != bean.getStartTime()) {
			objectList.add(bean.getStartTime());
		} else {
			objectList.add(DateUtil.getDates(true, 0));
		}

		statusSql.append(" and m.createTime <= ? ");
		countSql.append(" and m.createTime <= ? ");
		if(null != bean.getEndTime()) {
			objectList.add(bean.getEndTime());
		} else {
			objectList.add(DateUtil.getDates(false, 0));
		}
		
		if(StringUtils.isNotBlank(bean.getLogisticsId())) {
			statusSql.append(" and m.logisticId = ? ");
			countSql.append(" and m.logisticId = ? ");
			objectList.add(bean.getLogisticsId());
		}
		
		statusSql.append(" group by m.productId ");
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalProductStatistics> list = this.findDataSta(page, bean);
		page.setResult(list);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalLogistics> findAllLogistics(String hospitalId) {
		return this.iBaseDao.getBySql("from MedicalLogistics m where m.hospitalId = ? ", null, hospitalId);
	}
	
	/**
	 * @desc 封装产品销售量统计信息
	 * @param page
	 * @return list
	 * @author dangjh
	 * @datetime 2014年12月12日 下午4:49:01
	 */
	private List<MedicalProductStatistics> findDataSta(Page page, MedicalProductStatistics bean) {
		List<MedicalProductStatistics> list = new ArrayList<MedicalProductStatistics>();
		Date startTime = DateUtil.getDates(true, 0);
		Date endTime = DateUtil.getDates(false, 0);
		if(null != bean.getStartTime()) {
			startTime = bean.getStartTime();
		}
		if(null != bean.getEndTime()) {
			endTime	= bean.getEndTime();
		}
		for(Object obj : page.getResult()) {
			MedicalProductStorage storage = (MedicalProductStorage) obj;
			MedicalProductStatistics product = new MedicalProductStatistics();
			
			product.setProductNo(storage.getSerialNum());
			product.setProductName(storage.getProductName());
			if(null != storage.getProductPrice()) {
				product.setProductPrice(storage.getProductPrice());
			}
			// 发货部分
			Object object1 = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.productNum) from MedicalProductStorage m "
					+ " where m.productId = ? and m.status = ? and m.createTime >= ? and m.createTime <= ? ", storage.getProductId(), SystemConfig.HAS_DELIVERY,
					startTime, endTime);
			Object[] obj1 = (Object[]) object1;
			BigDecimal invoiceMoney = new BigDecimal(0);
			if(null != obj1[0]) {
				invoiceMoney = new BigDecimal(obj1[0].toString());
			}
			product.setInvoiceMoney(invoiceMoney);
			int invoiceNumber = 0;
			if(null != obj1[1]) {
				invoiceNumber = Integer.parseInt(obj1[1].toString());
			}
			product.setInvoiceNumber(invoiceNumber);
			
			// 退单部分
			Object object2 = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.productNum) from MedicalProductStorage m "
					+ " where m.productId = ? and m.status = ? and m.createTime >= ? and m.createTime <= ? ", storage.getProductId(), SystemConfig.HAS_CHARGEBACK, 
					startTime, endTime);
			Object[] obj2 = (Object[]) object2;
			BigDecimal backMoney = new BigDecimal(0);
			if(null != obj2[0]) {
				backMoney = new BigDecimal(obj2[0].toString());
			}
			product.setBackMoney(backMoney);
			int backNumber = 0;
			if(null != obj2[1]) {
				backNumber = Integer.parseInt(obj2[1].toString());
			}
			product.setBackNumber(backNumber);
			
			// 签收部分
			Object object3 = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.productNum) from MedicalProductStorage m "
					+ " where m.productId = ? and m.status = ? and m.createTime >= ? and m.createTime <= ? ", storage.getProductId(), SystemConfig.HAS_SIGNED, 
					startTime, endTime);
			Object[] obj3 = (Object[]) object3;
			BigDecimal signMoney = new BigDecimal(0);
			if(null != obj3[0]) {
				signMoney = new BigDecimal(obj3[0].toString());
			}
			product.setSignMoney(signMoney);
			int signNumber = 0;
			if(null != obj3[1]) {
				signNumber = Integer.parseInt(obj3[1].toString());
			}
			product.setSignNumber(signNumber);
			
			// 对账部分
			Object object4 = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.productNum) from MedicalProductStorage m "
					+ " where m.productId = ? and m.status = ? and m.createTime >= ? and m.createTime <= ? ", storage.getProductId(), SystemConfig.HAS_RECONCILED, 
					startTime, endTime);
			Object[] obj4 = (Object[]) object4;
			BigDecimal reconMoney = new BigDecimal(0);
			if(null != obj4[0]) {
				reconMoney = new BigDecimal(obj4[0].toString());
			}
			product.setReconMoney(reconMoney);
			int reconNumber = 0;
			if(null != obj4[1]) {
				reconNumber = Integer.parseInt(obj4[1].toString());
			}
			product.setReconNumber(reconNumber);
			
			// 回款部分
			Object object5 = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.productNum) from MedicalProductStorage m "
					+ " where m.productId = ? and m.status = ? and m.createTime >= ? and m.createTime <= ? ", storage.getProductId(), SystemConfig.HAS_SECTIONED,
					startTime, endTime);
			Object[] obj5 = (Object[]) object5;
			BigDecimal sectionMoney = new BigDecimal(0);
			if(null != obj5[0]) {
				sectionMoney = new BigDecimal(obj5[0].toString());
			}
			product.setSectionMoney(sectionMoney);
			int sectionNumber = 0;
			if(null != obj5[1]) {
				sectionNumber = Integer.parseInt(obj5[1].toString());
			}
			product.setSectionNumber(sectionNumber);
			
			// 发货总数=退单+发货+签收+对账+回款
			int invoiceTotalNumber = backNumber + invoiceNumber + signNumber + reconNumber + sectionNumber;
			product.setInvoiceTotalNumber(invoiceTotalNumber);
			BigDecimal invoiceTotal = backMoney.add(invoiceMoney).add(signMoney).add(reconMoney).add(sectionMoney);
			product.setInvoiceTotal(invoiceTotal);
			list.add(product);
		}
		return list;
	}
}
